import sqlite3
import csv
conn = sqlite3.connect('D:/zy/nct.db')
c = conn.cursor()

csvfile = open('./firmlist/firmlist_sponsor.csv', 'r')
reader = csv.DictReader(csvfile)

for row in reader:
    agency = row['sponsor']
    print(row['nct_id'] + 'agency: ' + agency)
    sql = "select \
        sponsor, lead_sponsor_agency, collaborator_agency, nct_id,condition,phase,primary_purpose,study_type,intervention,\
        keyword,primary_outcome_enrollment,start_date,completion_date_type,completion_date,primary_completion_date_type,primary_completion_date,\
        overall_status,study_first_submitted,lead_sponsor_class,collaborator_class \
        from nct_data \
        where lead_sponsor_agency like '%{agency}%' \
        or collaborator_agency like '%{agency}%' \
        or sponsor like '%{agency}%'".format(agency=agency)
    # print(sql)

    try:
        c.execute(sql)  
    except Exception as e:
        print('Select Error:'+str(e))
        continue

    # record = c.fetchone()
    records = c.fetchall()
    print('fetch records len: ' + str(len(records)))
    for record in records:
    # while record:
        # print('fetch record:' + record[3])
        source_dummy = 0
        lead_dummy = 0
        collaborator_dummy = 0
        if agency in record[0] : source_dummy = 1
        if agency in record[1] : lead_dummy = 1
        if agency in record[2] : collaborator_dummy = 1
        data = {
            'nct_id': row['nct_id'],
            'sponsor': agency,
            'lead_dummy': lead_dummy,
            'collaborator_dummy': collaborator_dummy,
            'source_dummy': source_dummy,
            '_sponsor': record[0],
            '_lead_sponsor_agency': record[1],
            '_collaborator_agency': record[2],
            '_nct_id': record[3],
            '_condition': record[4],
            '_phase': record[5],
            '_primary_purpose': record[6],
            '_study_type': record[7],
            '_intervention': record[8],
            '_keyword': record[9],
            '_primary_outcome_enrollment': record[10],
            '_start_date': record[11],
            '_completion_date_type ': record[12],
            '_completion_date': record[13],
            '_primary_completion_date_type': record[14],
            '_primary_completion_date': record[15],
            '_overall_status': record[16],
            '_study_first_submitted': record[17],
            '_lead_sponsor_class': record[18],
            '_collaborator_agency_type': record[19]
        }
        table = 'firmlist3'
        keys = ', '.join(data.keys())
        values = ', '.join(['?']*len(data))
        sql2 = 'insert into {table}({keys}) values ({values})'.format(table = table, keys = keys, values= values)
        # print(sql2)
        c.execute(sql2, tuple(data.values()))
        
        # record = c.fetchone()
conn.commit()        
conn.close()
csvfile.close()  
